package cn.com.callback

import cn.com.elite.EliteBean
import cn.com.elite.Util
import cn.com.kh.KHUtil
import cn.com.service.GeneralService
import net.sf.json.JSONArray
import net.sf.json.JSONObject
import org.apache.commons.logging.Log
import org.apache.commons.logging.LogFactory
import org.springframework.jdbc.core.JdbcTemplate

import javax.servlet.http.HttpServletRequest
import javax.servlet.http.HttpServletResponse
import javax.sql.DataSource
import java.text.SimpleDateFormat

/**
 *
 * @Title: cn.com.callback-apiTankCleanSms
 * @Description:ngsapitask-sw 水池箱清洗
 * @Author: hucf
 * @Date 2023/4/24 15:41
 *
 */
class apiTankCleanSms implements ServiceBackInvoke {
    private static final Log log = LogFactory.getLog(apiTankCleanSms.class);
    long startTimeStamp = System.currentTimeMillis();
    List<String> sqlList = new ArrayList<>();

    @Override
    String doCallBack(EliteBean eliteBean, HttpServletRequest request, HttpServletResponse response) {
        response.setCharacterEncoding("UTF-8");
        response.setContentType("application/json;charset=UTF-8");
        //封装返回报文对象
        JSONObject resultJson = new JSONObject();
        //获取开始时间戳
        String poolCleanBody = "";
        JSONObject json = null;
        //获取通知返回结果
        List list = null;
        //数据源
        String dataSource = "dataSource";
        //拼接获取各类调用必填参数信息
        String isCheckNull = "";
        //拼接业务提醒内容
        String promptContent = "";
        SimpleDateFormat sFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        SimpleDateFormat fm = new SimpleDateFormat("yyyy-MM-dd");
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("MM-dd");
        SimpleDateFormat sf = new SimpleDateFormat("HH:mm");
        //参数封装类
        WaterTankVo waterTankVo = new WaterTankVo();
        waterTankVo.setCreatedBy("SELITE");
        try {
            waterTankVo.setRecGuid(UUID.randomUUID().toString().toUpperCase());
            //获取入参报文
            poolCleanBody = Util.getRequestBody(request, 'utf-8')
            log.debug("入参报文：" + poolCleanBody)
            JSONObject poolCleanObject = JSONObject.fromObject(poolCleanBody);
            //水池箱清洗申请类型
            waterTankVo.setApplyCleanType(poolCleanObject.getString("applyCleanType"));
            //标题
            waterTankVo.setPlanTitle(poolCleanObject.getString("planTitle"));
            //OA单号
            waterTankVo.setOAOrderNumber(poolCleanObject.getString("OAOrderNumber"));
            //计划主单号
            waterTankVo.setPlanMainNumber(poolCleanObject.getString("planMainNumber"));
            //审核员名称
            waterTankVo.setAuditorName(poolCleanObject.getString("auditorName"));
            //申请日期
            waterTankVo.setApplyDate(poolCleanObject.getString("applyDate"));
            //分管负责人
            waterTankVo.setPersonCharge(poolCleanObject.getString("personCharge"));
            if (waterTankVo.getApplyCleanType() == "add") {
                //分管领导
                waterTankVo.setLeaderCharge(poolCleanObject.getString("leaderCharge"));
                isCheckNull += KHUtil.isNullMSG("leaderCharge", waterTankVo.getLeaderCharge());
            }
            //现场负责人
            waterTankVo.setWorksiteCharge(poolCleanObject.getString("worksiteCharge"));
            //联系电话
            waterTankVo.setWorksiteContact(poolCleanObject.getString("worksiteContact"));
            isCheckNull += KHUtil.isNullMSG("worksiteCharge", waterTankVo.getWorksiteCharge());
            isCheckNull += KHUtil.isNullMSG("worksiteContact", waterTankVo.getWorksiteContact());
            isCheckNull += KHUtil.isNullMSG("planTitle", waterTankVo.getPlanTitle());
            isCheckNull += KHUtil.isNullMSG("OAOrderNumber", waterTankVo.getOAOrderNumber());
            isCheckNull += KHUtil.isNullMSG("auditorName", waterTankVo.getAuditorName());
            isCheckNull += KHUtil.isNullMSG("applyDate", waterTankVo.getApplyDate());
            isCheckNull += KHUtil.isNullMSG("planMainNumber", waterTankVo.getPlanMainNumber());
            isCheckNull += KHUtil.isNullMSG("personCharge", waterTankVo.getPersonCharge());
            //水池箱申请信息操作

            //水池箱列表
            waterTankVo.setCleanPlanList(JSONArray.fromObject(poolCleanObject.getString("cleanPlanList")));
            String sDateCopy = "";
            for (int i = 0; i < waterTankVo.getCleanPlanList().size(); i++) {
                JSONObject cleanPlanObject = waterTankVo.getCleanPlanList().get(i);
                //清洗计划单号(唯一)
                waterTankVo.setCleanPlanGuid(cleanPlanObject.getString("cleanPlanGuid"));
                //小区编码
                waterTankVo.setCommunityCode(cleanPlanObject.getString("communityCode"));
                //小区名称
                waterTankVo.setCommunityName(cleanPlanObject.getString("communityName"));
                //水池箱编码
                waterTankVo.setTankCode(cleanPlanObject.getString("tankCode"));
                //水池箱名称
                waterTankVo.setTankName(cleanPlanObject.getString("tankName"));
                //是否停水
                waterTankVo.setIsCutOffWater(cleanPlanObject.getString("isCutOffWater"));
                //是否影响消防用水
                waterTankVo.setIsAffectFireWater(cleanPlanObject.getString("isAffectFireWater"));
                //计划开始日期
                waterTankVo.setPlanStartDate(cleanPlanObject.getString("planDate"));
                //计划结束日期
                if (waterTankVo.getApplyCleanType() != "change") {
                    waterTankVo.setEndDate(cleanPlanObject.get("endDate"));
                    isCheckNull = KHUtil.isNullMSG("endDate", waterTankVo.getEndDate());
                }
                //开始时间
                waterTankVo.setStartTime(cleanPlanObject.getString("startTime"));
                //结束时间
                waterTankVo.setEndTime(cleanPlanObject.getString("endTime"));
                //审批状态
                waterTankVo.setApprovalStatus(cleanPlanObject.getString("approvalStatus"));
                if (waterTankVo.getApplyCleanType() != "add") {
                    //原清洗计划
                    waterTankVo.setOriginalCleanPlanProcessId(cleanPlanObject.getString("OriginalCleanPlanProcess_id"));
                    isCheckNull += KHUtil.isNullMSG("OriginalCleanPlanProcessId", waterTankVo.getOriginalCleanPlanProcessId());
                }
                //短信内容
                waterTankVo.setSmsContent(cleanPlanObject.getString("smsContent"));
                //分管领导
                if (waterTankVo.getApplyCleanType() == "change") {
                    //调整后计划日期
                    waterTankVo.setAfterAdjustPlanDate(cleanPlanObject.getString("afterAdjustPlanDate"));
                    //调整后结束日期
                    waterTankVo.setAfterAdjustEndDate(cleanPlanObject.getString("afterAdjustEndDate"));
                    //调整后开始时间
                    waterTankVo.setAfterAdjustStartTime(cleanPlanObject.getString("afterAdjustStartTime"));
                    //调整后结束时间
                    waterTankVo.setAfterAdjustEndTime(cleanPlanObject.getString("afterAdjustEndTime"));
                    isCheckNull += KHUtil.isNullMSG("afterAdjustPlanDate", waterTankVo.getAfterAdjustPlanDate());
                    isCheckNull += KHUtil.isNullMSG("afterAdjustEndDate", waterTankVo.getAfterAdjustEndDate());
                    isCheckNull += KHUtil.isNullMSG("afterAdjustStartTime", waterTankVo.getAfterAdjustStartTime());
                    isCheckNull += KHUtil.isNullMSG("afterAdjustEndTime", waterTankVo.getAfterAdjustEndTime());
                }
                isCheckNull += KHUtil.isNullMSG("cleanPlanGuid", waterTankVo.getCleanPlanGuid());
                //isCheckNull += KHUtil.isNullMSG("communityCode", waterTankVo.getCommunityCode());
                isCheckNull += KHUtil.isNullMSG("communityName", waterTankVo.getCommunityName());
                isCheckNull += KHUtil.isNullMSG("tankCode", waterTankVo.getTankCode());
                isCheckNull += KHUtil.isNullMSG("tankName", waterTankVo.getTankName());
                isCheckNull += KHUtil.isNullMSG("isCutOffWater", waterTankVo.getIsCutOffWater());
                isCheckNull += KHUtil.isNullMSG("isAffectFireWater", waterTankVo.getIsAffectFireWater());
                isCheckNull += KHUtil.isNullMSG("planDate", waterTankVo.getPlanStartDate());
                isCheckNull += KHUtil.isNullMSG("startTime", waterTankVo.getStartTime());
                isCheckNull += KHUtil.isNullMSG("endTime", waterTankVo.getEndTime());
                isCheckNull += KHUtil.isNullMSG("approvalStatus", waterTankVo.getApprovalStatus());
                isCheckNull += KHUtil.isNullMSG("smsContent", waterTankVo.getSmsContent());
                //拼接业务提醒
                Date planDate = fm.parse(waterTankVo.getPlanStartDate());
                String sDate = simpleDateFormat.format(planDate);
                if (sDate != sDateCopy) {
                    promptContent = "";
                }
                promptContent += sDate + "清洗水池：\n";
                sDateCopy = sDate;
                log.debug("sDateCopy" + i + ":" + sDateCopy);
                /*判断清洗计划单号是否已存在
                int isExistCleanPlanGuid = isExistCleanPlan(waterTankVo, eliteBean, dataSource);
                if (isExistCleanPlanGuid > 0) {
                    resultJson.put("code", "-2");
                    resultJson.put("msg", "" + waterTankVo.getCleanPlanGuid() + "清洗计划单号已存在！调用问题处理时长(毫秒):" + (System.currentTimeMillis() - startTimeStamp));
                    return resultJson.toString();
                }*/
                int oldCleanPlanGuid = getOriginalCleanPlanProcessId(waterTankVo, eliteBean, dataSource);
                if (oldCleanPlanGuid == 0 && waterTankVo.getApplyCleanType() != "add") {
                    resultJson.put("code", "-2");
                    resultJson.put("msg", "" + waterTankVo.getCleanPlanGuid() + "原清洗计划单号不存在无法进行改期或取消操作！调用问题处理时长(毫秒):" + (System.currentTimeMillis() - startTimeStamp));
                    return resultJson.toString();
                }
                //影响小区数据
                waterTankVo.setEffectCommunityList(JSONArray.fromObject(cleanPlanObject.getString("effectCommunityList")));
                waterTankVo.setCommunityCount(waterTankVo.getEffectCommunityList().size());
                log.debug("影响小区数量：" + waterTankVo.getCommunityCount());
                int userCount = 0;
                for (int j = 0; j < waterTankVo.getEffectCommunityList().size(); j++) {
                    JSONObject effectCommunityJson = waterTankVo.getEffectCommunityList().get(j);
                    waterTankVo.setEffectCommunityCode(effectCommunityJson.getString("effectCommunityCode"));
                    waterTankVo.setEffectCommunityName(effectCommunityJson.getString("effectCommunityName"));
                    log.debug("effectCommunityName:" + waterTankVo.getEffectCommunityName());
                    //isCheckNull += KHUtil.isNullMSG("effectCommunityCode", waterTankVo.getEffectCommunityCode());
                    isCheckNull += KHUtil.isNullMSG("effectCommunityName", waterTankVo.getEffectCommunityName());
                    Date simpleStartTime = sFormat.parse(waterTankVo.getStartTime());
                    Date simpleEndTime = sFormat.parse(waterTankVo.getEndTime());
                    String st = sf.format(simpleStartTime);
                    String et = sf.format(simpleEndTime);
                    promptContent += st + "-" + et + "\n";
                    promptContent += waterTankVo.getEffectCommunityName() + "\n";
                    if (j == waterTankVo.getEffectCommunityList().size() - 1) {
                        waterTankVo.setOperator("busy");
                        waterTankVo.setPromptContent(promptContent);
                        sqlList.add(doWaterTank(waterTankVo, eliteBean));
                    }
                    //影响小区信息操作
                    waterTankVo.setOperator("community");
                    sqlList.add(doWaterTank(waterTankVo, eliteBean));
                    waterTankVo.setEffectUserList(JSONArray.fromObject(effectCommunityJson.getString("effectUserList")));
                    userCount += waterTankVo.getEffectUserList().size();

                    for (int k = 0; k < waterTankVo.getEffectUserList().size(); k++) {
                        JSONObject effectUserJson = waterTankVo.getEffectUserList().get(k);
                        waterTankVo.setEffectUserCode(effectUserJson.getString("effectUserCode"));
                        waterTankVo.setEffectUserPhone(effectUserJson.getString("effectUserPhone"));
                        isCheckNull += KHUtil.isNullMSG("effectUserCode", waterTankVo.getEffectUserCode());
                        isCheckNull += KHUtil.isNullMSG("effectUserPhone", waterTankVo.getEffectUserPhone());
                        //影响用户信息操作
                        waterTankVo.setOperator("user");
                        sqlList.add(doWaterTank(waterTankVo, eliteBean));
                    }
                }
                waterTankVo.setUserCount(userCount);
                log.debug("影响用户数量：" + waterTankVo.getUserCount());
                //附件处理
                waterTankVo.setAnnexList(JSONArray.fromObject(cleanPlanObject.getString("appendix")));
                waterTankVo.setAppendixCount(waterTankVo.getAnnexList().size());
                log.debug("附件数量：" + waterTankVo.getAppendixCount());
                for (int l = 0; l < waterTankVo.getAnnexList().size(); l++) {
                    JSONObject annexJson = waterTankVo.getAnnexList().get(l);
                    waterTankVo.setAppendixURL(annexJson.getString("appendixURL"));
                    waterTankVo.setAppendixName(annexJson.getString("appendixName"));
                    //附件信息操作
                    waterTankVo.setOperator("annex");
                    sqlList.add(doWaterTank(waterTankVo, eliteBean));
                }
                //水池箱列表信息操作
                waterTankVo.setOperator("TankList");
                sqlList.add(doWaterTank(waterTankVo, eliteBean));
                waterTankVo.setOperator("his");
                sqlList.add(doWaterTank(waterTankVo, eliteBean));
            }
            if (isCheckNull != "") {
                resultJson.put("code", "-2");
                resultJson.put("msg", "调用参数非空校验异常:" + isCheckNull + "，调用处理时长(毫秒):" + (System.currentTimeMillis() - startTimeStamp));
                return resultJson.toString();
            }
            //发送短信
            waterTankVo.setOperator("sms");
            sqlList.add(doWaterTank(waterTankVo, eliteBean));
            log.debug("拼接业务提醒数据：" + promptContent);
            /*int isExistOA = isExistOAORMainNumber(waterTankVo, eliteBean, dataSource);
            if (isExistOA > 0) {
                resultJson.put("code", "-2");
                resultJson.put("msg", "" + waterTankVo.getOAOrderNumber() + "OA单号或计划主单号已存在！调用问题处理时长(毫秒):" + (System.currentTimeMillis() - startTimeStamp));
                return resultJson.toString();
            }*/
            waterTankVo.setOperator("TankApply");
            sqlList.add(doWaterTank(waterTankVo, eliteBean));
            log.debug("当前批次已经存在执行结果SQL:" + sqlList);
            int[] updateRow = updateSql(sqlList, dataSource);
            log.debug("执行到数据库数据：" + updateRow);
            resultJson.put("code", "1");
            resultJson.put("msg", "计划单号:" + waterTankVo.getPlanMainNumber() + " Succ处理成功、调用问题处理时长(毫秒):" + (System.currentTimeMillis() - startTimeStamp).toString());
            resultJson.put("data", poolCleanObject);
        } catch (Exception e) {
            log.error("接口处理异常" + e.getMessage());
            resultJson.put("code", "-2");
            resultJson.put("msg", "Exception:可能是JSON格式问题、连接数据库问题、调用问题处理时长(毫秒):" + (System.currentTimeMillis() - startTimeStamp));
        }
        return resultJson.toString();
    }

    /**
     * sql集合
     * @param waterTankVo
     * @param eliteBean
     * @param dataSource
     * @return
     */
    public String doWaterTank(WaterTankVo waterTankVo, EliteBean eliteBean) {
        String waterTankSql = "";
        if (waterTankVo.getApplyCleanType() == "add") {
            if (waterTankVo.getOperator() == "TankApply") {
                //计划申请主表信息
                waterTankSql = '''insert into swg_water_tank_apply
                              (rec_guid,
                               plan_title,
                               OA_order_number,
                               plan_main_number,
                               auditor_name,
                               apply_date,
                               person_charge,
                               leader_charge,
                               worksiteCharge,
                               worksiteContact,
                               created_by,
                               created_date,
                               apply_clean_type)
                            values
                               (\'''' + waterTankVo.getRecGuid() + '''\',
                                \'''' + waterTankVo.getPlanTitle() + '''\',
                                \'''' + waterTankVo.getOAOrderNumber() + '''\',
                                \'''' + waterTankVo.getPlanMainNumber() + '''\',
                                \'''' + waterTankVo.getAuditorName() + '''\',
                                \'''' + waterTankVo.getApplyDate() + '''\',
                                \'''' + waterTankVo.getPersonCharge() + '''\',
                                \'''' + waterTankVo.getLeaderCharge() + '''\',
                                \'''' + waterTankVo.getWorksiteCharge() + '''\',
                                \'''' + waterTankVo.getWorksiteContact() + '''\',
                                \'''' + waterTankVo.getCreatedBy() + '''\' ,
                                GETDATE() ,
                                \'''' + waterTankVo.getApplyCleanType() + '''\' )''';
            }
            if (waterTankVo.getOperator() == "TankList") {
                //水池箱列表明细计划申请
                waterTankSql = '''INSERT INTO swg_waterTank
                              (rec_guid,
                               clean_plan_guid,
                               community_code,
                               community_name,
                               tank_code,
                               tank_name,
                               is_cutoff_water,
                               is_affect_fire_water,
                               plan_start_date,
                               plan_end_date,
                               start_time,
                               end_time,
                               approval_status,
                               effect_community_count,
                               effect_user_count,
                               sms_content,
                               appendix_count,
                               plan_main_number,
                               sms_guid,
                               apply_clean_type,
                               created_date,
                               created_by)
                             VALUES
                               (\'''' + UUID.randomUUID().toString().toUpperCase() + '''\',
                               \'''' + waterTankVo.getCleanPlanGuid() + '''\',
                               \'''' + waterTankVo.getCommunityCode() + '''\',
                               \'''' + waterTankVo.getCommunityName() + '''\',
                               \'''' + waterTankVo.getTankCode() + '''\',
                               \'''' + waterTankVo.getTankName() + '''\',
                               \'''' + waterTankVo.getIsCutOffWater() + '''\',
                               \'''' + waterTankVo.getIsAffectFireWater() + '''\',
                               \'''' + waterTankVo.getPlanStartDate() + '''\',
                               \'''' + waterTankVo.getEndDate() + '''\',
                               \'''' + waterTankVo.getStartTime() + '''\',
                               \'''' + waterTankVo.getEndTime() + '''\',
                               \'''' + waterTankVo.getApprovalStatus() + '''\',
                               \'''' + waterTankVo.getCommunityCount() + '''\',
                               \'''' + waterTankVo.getUserCount() + '''\',
                               \'''' + waterTankVo.getSmsContent() + '''\',
                               \'''' + waterTankVo.getAppendixCount() + '''\', 
                               \'''' + waterTankVo.getPlanMainNumber() + '''\',
                               \'''' + waterTankVo.getRecGuid() + '''\',
                               \'''' + waterTankVo.getApplyCleanType() + '''\',
                               GETDATE() ,
                               \'''' + waterTankVo.getApplyCleanType() + '''\')''';
            }
        } else if (waterTankVo.getApplyCleanType() != "add") {
            if (waterTankVo.getOperator() == "TankApply") {
                //改期/取消申请主表信息
                waterTankSql = '''insert into swg_water_tank_apply
                              (rec_guid,
                               plan_title,
                               OA_order_number,
                               plan_main_number,
                               auditor_name,
                               apply_date,
                               person_charge,
                               worksiteCharge,
                               worksiteContact,
                               created_by,
                               created_date,
                               apply_clean_type)
                            values
                              (\'''' + waterTankVo.getRecGuid() + '''\',
                               \'''' + waterTankVo.getPlanTitle() + '''\',
                               \'''' + waterTankVo.getOAOrderNumber() + '''\',
                               \'''' + waterTankVo.getPlanMainNumber() + '''\',
                               \'''' + waterTankVo.getAuditorName() + '''\',
                               \'''' + waterTankVo.getApplyDate() + '''\',
                               \'''' + waterTankVo.getPersonCharge() + '''\',
                               \'''' + waterTankVo.getWorksiteCharge() + '''\',
                               \'''' + waterTankVo.getWorksiteContact() + '''\',
                               \'''' + waterTankVo.getCreatedBy() + '''\' ,
                               GETDATE() ,
                               \'''' + waterTankVo.getApplyCleanType() + '''\' )''';
            }
            if (waterTankVo.getOperator() == "TankList") {
                if (waterTankVo.getApplyCleanType() == "change") {
                    //水池箱列表明细改期申请
                    waterTankSql = '''INSERT INTO swg_waterTank
                               (rec_guid,
                                clean_plan_guid,
                                community_code,
                                community_name,
                                tank_code,
                                tank_name,
                                is_cutoff_water,
                                is_affect_fire_water,
                                plan_start_date,
                                start_time,
                                end_time,
                                approval_status,
                                originalCleanPlanProcess_id,
                                afterAdjustPlanDate,
                                afterAdjustEndDate,
                                afterAdjustStartTime,
                                afterAdjustEndTime,
                                effect_community_count,
                                effect_user_count,
                                sms_content,
                                appendix_count,
                                plan_main_number,
                                sms_guid,
                                apply_clean_type,
                                created_date,
                                created_by)
                              VALUES
                               (\'''' + UUID.randomUUID().toString().toUpperCase() + '''\',
                                \'''' + waterTankVo.getCleanPlanGuid() + '''\',
                                \'''' + waterTankVo.getCommunityCode() + '''\',
                                \'''' + waterTankVo.getCommunityName() + '''\',
                                \'''' + waterTankVo.getTankCode() + '''\',
                                \'''' + waterTankVo.getTankName() + '''\',
                                \'''' + waterTankVo.getIsCutOffWater() + '''\',
                                \'''' + waterTankVo.getIsAffectFireWater() + '''\',
                                \'''' + waterTankVo.getPlanStartDate() + '''\',
                                \'''' + waterTankVo.getStartTime() + '''\',
                                \'''' + waterTankVo.getEndTime() + '''\', 
                                \'''' + waterTankVo.getApprovalStatus() + '''\', 
                                \'''' + waterTankVo.getOriginalCleanPlanProcessId() + '''\',
                                \'''' + waterTankVo.getAfterAdjustPlanDate() + '''\',
                                \'''' + waterTankVo.getAfterAdjustEndDate() + '''\',
                                \'''' + waterTankVo.getAfterAdjustStartTime() + '''\',
                                \'''' + waterTankVo.getAfterAdjustEndTime() + '''\',
                                \'''' + waterTankVo.getCommunityCount() + '''\', 
                                \'''' + waterTankVo.getUserCount() + '''\',
                                \'''' + waterTankVo.getSmsContent() + '''\', 
                                \'''' + waterTankVo.getAppendixCount() + '''\',
                                \'''' + waterTankVo.getPlanMainNumber() + '''\',
                                \'''' + waterTankVo.getRecGuid() + '''\',
                                \'''' + waterTankVo.getApplyCleanType() + '''\',
                                GETDATE() ,
                                \'''' + waterTankVo.getCreatedBy() + '''\')''';
                }
                if (waterTankVo.getApplyCleanType() == "cancel") {
                    //水池箱列表明细取消申请
                    waterTankSql = '''INSERT INTO swg_waterTank
                             (rec_guid,
                              clean_plan_guid,
                              community_code,
                              community_name,
                              tank_code,
                              tank_name,
                              is_cutoff_water,
                              is_affect_fire_water,
                              plan_start_date,
                              plan_end_date,
                              start_time,
                              end_time,
                              approval_status,
                              originalCleanPlanProcess_id,                                                  
                              effect_community_count,
                              effect_user_count,
                              sms_content,
                              appendix_count,
                              plan_main_number,
                              sms_guid,
                              apply_clean_type,
                              created_date,
                              created_by)
                             VALUES
                                (\'''' + UUID.randomUUID().toString().toUpperCase() + '''\',
                                \'''' + waterTankVo.getCleanPlanGuid() + '''\',
                                \'''' + waterTankVo.getCommunityCode() + '''\',
                                \'''' + waterTankVo.getCommunityName() + '''\',
                                \'''' + waterTankVo.getTankCode() + '''\',
                                \'''' + waterTankVo.getTankName() + '''\',
                                \'''' + waterTankVo.getIsCutOffWater() + '''\',
                                \'''' + waterTankVo.getIsAffectFireWater() + '''\',
                                \'''' + waterTankVo.getPlanStartDate() + '''\',
                                \'''' + waterTankVo.getEndDate() + '''\',
                                \'''' + waterTankVo.getStartTime() + '''\',
                                \'''' + waterTankVo.getEndTime() + '''\',
                                \'''' + waterTankVo.getApprovalStatus() + '''\', 
                                \'''' + waterTankVo.getOriginalCleanPlanProcessId() + '''\', 
                                \'''' + waterTankVo.getCommunityCount() + '''\',
                                \'''' + waterTankVo.getUserCount() + '''\',
                                \'''' + waterTankVo.getSmsContent() + '''\', 
                                \'''' + waterTankVo.getAppendixCount() + '''\',
                                \'''' + waterTankVo.getPlanMainNumber() + '''\',
                                \'''' + waterTankVo.getRecGuid() + '''\',
                                \'''' + waterTankVo.getApplyCleanType() + '''\',
                                GETDATE() ,
                                \'''' + waterTankVo.getCreatedBy() + '''\')''';
                }
            }
        }
        if (waterTankVo.getOperator() == "his") {
            if (waterTankVo.getApplyCleanType() == "change") {
                //水池箱明细列表历史
                waterTankSql = '''INSERT INTO swg_waterTankHistory(
                                rec_guid,
                                clean_plan_guid,
                                community_code,
                                community_name,
                                tank_code,
                                tank_name,
                                is_cutoff_water,
                                is_affect_fire_water,
                                plan_start_date,
                                start_time,
                                end_time,
                                approval_status,
                                effect_community_count,
                                effect_user_count,
                                apply_clean_type,
                                created_date,
                                created_by)
                            values(
                                \'''' + UUID.randomUUID().toString().toUpperCase() + '''\',  
                                \'''' + waterTankVo.getCleanPlanGuid() + '''\',
                                \'''' + waterTankVo.getCommunityCode() + '''\',
                                \'''' + waterTankVo.getCommunityName() + '''\',
                                \'''' + waterTankVo.getTankCode() + '''\',
                                \'''' + waterTankVo.getTankName() + '''\',
                                \'''' + waterTankVo.getIsCutOffWater() + '''\',
                                \'''' + waterTankVo.getIsAffectFireWater() + '''\',
                                \'''' + waterTankVo.getPlanStartDate() + '''\',
                                \'''' + waterTankVo.getStartTime() + '''\',
                                \'''' + waterTankVo.getEndTime() + '''\',
                                \'''' + waterTankVo.getApprovalStatus() + '''\',
                                \'''' + waterTankVo.getCommunityCount() + '''\',
                                \'''' + waterTankVo.getUserCount() + '''\',
                                \'''' + waterTankVo.getApplyCleanType() + '''\',
                                GETDATE() ,
                                \'''' + waterTankVo.getCreatedBy() + '''\'
                            ) ''';
            } else {
                //水池箱明细列表历史
                waterTankSql = '''INSERT INTO swg_waterTankHistory(
                                rec_guid,
                                clean_plan_guid,
                                community_code,
                                community_name,
                                tank_code,
                                tank_name,
                                is_cutoff_water,
                                is_affect_fire_water,
                                plan_start_date,
                                plan_end_date,
                                start_time,
                                end_time,
                                approval_status,
                                effect_community_count,
                                effect_user_count,
                                apply_clean_type,
                                created_date,
                                created_by)
                            values(
                                \'''' + UUID.randomUUID().toString().toUpperCase() + '''\',  
                                \'''' + waterTankVo.getCleanPlanGuid() + '''\',
                                \'''' + waterTankVo.getCommunityCode() + '''\',
                                \'''' + waterTankVo.getCommunityName() + '''\',
                                \'''' + waterTankVo.getTankCode() + '''\',
                                \'''' + waterTankVo.getTankName() + '''\',
                                \'''' + waterTankVo.getIsCutOffWater() + '''\',
                                \'''' + waterTankVo.getIsAffectFireWater() + '''\',
                                \'''' + waterTankVo.getPlanStartDate() + '''\',
                                \'''' + waterTankVo.getEndDate() + '''\',
                                \'''' + waterTankVo.getStartTime() + '''\',
                                \'''' + waterTankVo.getEndTime() + '''\',
                                \'''' + waterTankVo.getApprovalStatus() + '''\',
                                \'''' + waterTankVo.getCommunityCount() + '''\',
                                \'''' + waterTankVo.getUserCount() + '''\',
                                \'''' + waterTankVo.getApplyCleanType() + '''\',
                                GETDATE() ,
                                \'''' + waterTankVo.getCreatedBy() + '''\'
                            ) '''
            }
        }
        if (waterTankVo.getOperator() == "user") {
            //影响用户记录
            waterTankSql = '''insert into swg_waterTankUser
                              (rec_guid,
                               clean_plan_guid,
                               effect_user_code,
                               effect_user_phone,
                               created_date,
                               created_by,
                               apply_clean_type)
                              values
                               (\'''' + UUID.randomUUID().toString().toUpperCase() + '''\',
                                \'''' + waterTankVo.getCleanPlanGuid() + '''\',
                                \'''' + waterTankVo.getEffectUserCode() + '''\',
                                \'''' + waterTankVo.getEffectUserPhone() + '''\',
                                getdate() ,
                                'SELITE' ,
                                \'''' + waterTankVo.getApplyCleanType() + '''\' )''';
        }
        if (waterTankVo.getOperator() == "community") {
            //影响小区记录
            waterTankSql = '''insert into swg_waterTankCommunity
                              (rec_guid,
                               clean_plan_guid,
                               effect_community_code,
                               effect_community_name,
                               created_date,
                               created_by,
                               apply_clean_type)
                             values
                               (\'''' + UUID.randomUUID().toString().toUpperCase() + '''\',
                                \'''' + waterTankVo.getCleanPlanGuid() + '''\',
                                \'''' + waterTankVo.getCommunityCode() + '''\',
                                \'''' + waterTankVo.getCommunityName() + '''\',
                                getdate() ,
                                'SELITE' ,
                                \'''' + waterTankVo.getApplyCleanType() + '''\' )''';
        }
        if (waterTankVo.getOperator() == "annex") {
            //附件信息
            waterTankSql = '''insert into swg_water_tank_annex(
                              rec_guid,
                              clean_plan_guid,
                              appendix_url,
                              appendix_name,
                              created_date,
                              created_by,
                              apply_clean_type)
                             values
                              (\'''' + UUID.randomUUID().toString().toUpperCase() + '''\',
                               \'''' + waterTankVo.getCleanPlanGuid() + '''\',
                               \'''' + waterTankVo.getAppendixURL() + '''\',
                               \'''' + waterTankVo.getAppendixName() + '''\',
                               getdate() ,
                               'SELITE' ,
                               \'''' + waterTankVo.getApplyCleanType() + '''\')''';
        }
        if (waterTankVo.getOperator() == "busy" && waterTankVo.getApplyCleanType() != "cancel") {
            //系统今日业务提醒数据
            waterTankSql = '''INSERT INTO prompt_content
                              (prompt_guid,
                               prompt_matter_id,
                               content,
                               priority,
                               createdate,
                               area,
                               createby,
                               creategroup,
                               modifytime,
                               area_type,
                               plan_start_date,
                               cutoff_type)
                            VALUES
                              (\'''' + UUID.randomUUID().toString().toUpperCase() + '''\',
                               'CA853A' ,
                               \'''' + waterTankVo.getPromptContent() + '''\',
                               '86876F' ,
                               getdate() ,
                               'JT0001' ,
                               'SELITE' ,
                               'SYSTEM' ,
                               getdate() ,
                               '475AE8F5-8C60-0947-96D6-D4999CE07DF4',
                               \'''' + waterTankVo.getStartTime() + '''\',3)''';
        }
        if (waterTankVo.getOperator() == "sms" && waterTankVo.getApplyCleanType() == "add") {
            //计划短信发送(提前24小时)
            waterTankSql = getDocumentOutSql(waterTankVo,1);
        }
        if (waterTankVo.getApplyCleanType() == "change" && waterTankVo.getOriginalCleanPlanProcessId() != "" && waterTankVo.getOperator() == "sms") {
            /*判断当前计划申请清洗流程中的短信是否已发出，
            已发出则立即发送改期短信；
            若未发出则废弃之前计划申请未发出的短信按照新的改期时间提前24小时发送生成短信发送任务*/
            int issueCount = ifIssue(waterTankVo, eliteBean, "dataSource");
            //将调整后的时间设置为发送时间
            waterTankVo.setStartTime(waterTankVo.getAfterAdjustStartTime());
            if (issueCount > 0) {
                //state = 2 表示废弃
                waterTankSql = '''update Documentout set status = 2,TRIALCOUNT = TRIALCOUNTLIMIT where sub_doctype = \'''' + waterTankVo.getOriginalCleanPlanProcessId() + '''\' and status = 3 ''';
                sqlList.add(waterTankSql);
                //改期短信发送(提前24小时)
                waterTankSql = getDocumentOutSql(waterTankVo,1);
            } else if (issueCount == 0) {
                //改期短信发送(立即发送)
                waterTankSql = getDocumentOutSql(waterTankVo,2);
            }
        }
        if (waterTankVo.getApplyCleanType() == "cancel" && waterTankVo.getOriginalCleanPlanProcessId() != "" && waterTankVo.getOperator() == "sms") {
            /*判断当前计划申请清洗流程中的短信是否已发出;
            已发出则立即发送取消短信;
            若未发出则废弃之前计划申请未发出的短信*/
            int issueCount = ifIssue(waterTankVo, eliteBean, "dataSource");
            if (issueCount > 0) {
                //state = 2 表示废弃
                waterTankSql = '''update Documentout set status = 2,TRIALCOUNT = TRIALCOUNTLIMIT where sub_doctype = \'''' + waterTankVo.getOriginalCleanPlanProcessId() + '''\' and status = 3 ''';
            } else if (issueCount == 0) {
                //取消短信发送(立即发送)
                waterTankSql = getDocumentOutSql(waterTankVo,2);
            }
        }
        return waterTankSql;
    }

    /**
     * 数据库批量操作
     * @param sqlList
     * @param eliteBean
     * @param dataSource
     * @return
     */
    public int[] updateSql(List sqlList, String dataSource) {
        return updateBatch(sqlList, dataSource);
    }

    /**
     * 判断清洗计划单号必填校验
     * @param waterTankVo
     * @param eliteBean
     * @param dataSource
     * @return
     */
    public int isExistCleanPlan(WaterTankVo waterTankVo, EliteBean eliteBean, String dataSource) {
        String sql = "";
        if (waterTankVo.getCleanPlanGuid() != null || waterTankVo.getCleanPlanGuid() != "") {
            sql = "select count(1) CON from swg_waterTank where clean_plan_guid = '" + waterTankVo.getCleanPlanGuid() + "'";
        } else {
            sql = "select 0 CON"
        }
        List getCountList = eliteBean.chaxun_sql(sql, dataSource);
        Integer count = getCountList[0].getAt("CON");
        return count;
    }

    /**
     * 判断计划短信是否发出
     * @param waterTankVo
     * @param eliteBean
     * @param dataSource
     * @return
     */
    public int ifIssue(WaterTankVo waterTankVo, EliteBean eliteBean, String dataSource) {
        Integer issueCount = 0;
        List smsIssueList = eliteBean.chaxun_sql("select count(1) CON from documentOut where sub_doctype = '" + waterTankVo.getOriginalCleanPlanProcessId() + "' and status = 3", dataSource);
        if (smsIssueList.size() > 0) {
            issueCount = smsIssueList[0].getAt("CON");
        }
        return issueCount;
    }

    /**
     * 判断OA单号、计划主单号必填校验
     * @param waterTankVo
     * @param eliteBean
     * @param dataSource
     * @return
     */
    public int isExistOAORMainNumber(WaterTankVo waterTankVo, EliteBean eliteBean, String dataSource) {
        String sql = "";
        if (waterTankVo.getOAOrderNumber() != null || waterTankVo.getPlanMainNumber() != null) {
            sql = "select count(1) CON from swg_water_tank_apply where (OA_order_number = '" + waterTankVo.getOAOrderNumber() + "' or plan_main_number = '" + waterTankVo.getPlanMainNumber() + "') and apply_clean_type = '" + waterTankVo.getApplyCleanType() + "'";
        } else {
            sql = "select 0 CON"
        }
        List getCountList = eliteBean.chaxun_sql(sql, dataSource);
        Integer count = getCountList[0].getAt("CON");
        return count;
    }

    /**
     * 改期或者取消时判断原清洗计划单号是否存在
     * @param waterTankVo
     * @param eliteBean
     * @param dataSource
     * @return
     */
    public int getOriginalCleanPlanProcessId(WaterTankVo waterTankVo, EliteBean eliteBean, String dataSource) {
        String sql = "select count(1) CON from swg_waterTank where clean_plan_guid = '" + waterTankVo.getOriginalCleanPlanProcessId() + "'";
        List getCountList = eliteBean.chaxun_sql(sql, dataSource);
        Integer count = getCountList[0].getAt("CON");
        return count;

    }
    /**
     * 批量事务执行
     * @param list
     * @param dataSourceID
     * @return
     */
    public int[] updateBatch(List list, String dataSourceID) {
        String[] sql = list.toArray();
        DataSource dataSource1 = (DataSource) GeneralService.wac.getBean(dataSourceID);
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource1);
        int[] resultList = jdbcTemplate.batchUpdate(sql);
        return resultList;
    }

    /**
     * 获取短信发送sql
     * @param waterTankVo
     * @return
     */
    public String getDocumentOutSql(WaterTankVo waterTankVo, Integer type) {
        String waterTankSql = "";
        if (type == 1) {
            waterTankSql = '''Insert Into Documentout
                                (Documentout_Guid,
                                Doctype,
                                Status,
                                Trialcount,
                                Trialcountlimit,
                                Documentpath,
                                Createdby,
                                Createddate,
                                Expectedstartdate,
                                Documentto,
                                Stringfield1,
                                Stringfield2,
                                Sms_Guid,
                                Ucisid,
                                Sub_Doctype) 
                                select newId(),
                                3,
                                3,
                                0,
                                5,
                                \'''' + waterTankVo.getSmsContent() + '''\',
                                created_by,
                                GETDATE(),
                                CONVERT ( datetime, CONVERT ( VARCHAR ( 20 ), CONVERT ( datetime, \'''' + waterTankVo.getStartTime() + '''\' ) - 1, 120 ) ),
                                effect_user_phone,
                                '智慧停水发送',
                                \'''' + waterTankVo.getRecGuid() + '''\',
                                \'''' + waterTankVo.getRecGuid() + '''\',
                                effect_user_code,
                                clean_plan_guid
                                from swg_waterTankUser
                                where clean_plan_guid = \'''' + waterTankVo.getCleanPlanGuid() + '''\'  and apply_clean_type = \'''' + waterTankVo.getApplyCleanType() + '''\'
                                ''';
        } else {
            waterTankSql = '''Insert Into Documentout
                                (Documentout_Guid,
                                Doctype,
                                Status,
                                Trialcount,
                                Trialcountlimit,
                                Documentpath,
                                Createdby,
                                Createddate,
                                Expectedstartdate,
                                Documentto,
                                Stringfield1,
                                Stringfield2,
                                Sms_Guid,
                                Ucisid,
                                Sub_Doctype) 
                                select newId(),
                                3,
                                3,
                                0,
                                5,
                                \'''' + waterTankVo.getSmsContent() + '''\',
                                created_by,
                                GETDATE(),
                                GETDATE(),
                                effect_user_phone,
                                '智慧停水发送',
                                \'''' + waterTankVo.getRecGuid() + '''\',
                                \'''' + waterTankVo.getRecGuid() + '''\',
                                effect_user_code,
                                clean_plan_guid
                                from swg_waterTankUser
                                where clean_plan_guid = \'''' + waterTankVo.getCleanPlanGuid() + '''\'  and apply_clean_type = \'''' + waterTankVo.getApplyCleanType() + '''\'
                                ''';
        }
        return waterTankSql
    }
}
